﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spWH_Log]
#-- Purpose:		Copies the maintenance log activity to the Warehouse
#--	Last Update:	05/09/2013
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spWH_Log]
AS

--- Declare Local Variables
DECLARE	@sSQL varchar(MAX)

IF EXISTS (SELECT TOP 1 * FROM sys.servers WHERE name = '$(MSXServer)')
  BEGIN
	SELECT	@sSQL = 'DECLARE @mnt_server_ssd_id int, @max_date datetime

SELECT	@mnt_server_ssd_id = [ssd_id]
FROM	[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblMSX_server_discovery]
WHERE	[ssd_sqlserver_name_ro] = @@SERVERNAME

SELECT	@max_date = MAX(mnt_action_date)
FROM	[$(MSXServer)].[$(MSXDatabase)].[dbo].[tblWH_Log]
WHERE	[mnt_server_ssd_id] = @mnt_server_ssd_id

IF @mnt_server_ssd_id IS NOT NULL
  BEGIN
	INSERT INTO [$(MSXServer)].[$(MSXDatabase)].[dbo].[tblWH_Log]
		(
		[mnt_server_ssd_id],
		[mnt_table_catalog],
		[mnt_table_schema],
		[mnt_table_name],
		[mnt_action_date],
		[mnt_action_type],
		[mnt_success],
		[mnt_results]
		)
	SELECT		@mnt_server_ssd_id,
				[mnt_table_catalog],
				[mnt_table_schema],
				[mnt_table_name],
				[mnt_action_date],
				[mnt_action_type],
				[mnt_success],
				[mnt_results]
	FROM		[dbo].[tblMaint_Log]
	WHERE		mnt_action_date > @max_date
				OR @max_date IS NULL
	ORDER BY	mnt_action_date
  END
ELSE
	RAISERROR(''This server has not been registered with the MSX Server'', 16, 1)'

	EXEC	(@sSQL)
  END